# demo01_mysql.py
import pymysql
# 1. 建立连接
conn = pymysql.connect(
    host="127.0.0.1", port=3306, user="root",
    db="tedu1", charset="utf8")
# 2. 创建游标(操作数据库)
cur = conn.cursor()
# -------------------3.1 创建表--------------------
# create_bumen="CREATE TABLE bumen(bumen_id INT,bumen_name VARCHAR(50));"
# cur.execute(create_bumen)  # 通过游标执行sql语句
# -------------------3.2 插入数据--------------------
# insert_bumen="INSERT INTO bumen VALUES(%s, %s);"
# cur.execute(insert_bumen, (1, "DEV"))
# cur.executemany(insert_bumen, [(2, "OPS"), (3, "UI"), (4, "HR")])
# -------------------3.3 查询数据--------------------
# select_bumen = "SELECT * FROM bumen;"
# cur.execute(select_bumen)  # 将select语句的查询结果集存储在游标中
# print(cur.fetchone())  # 抓取单条数据
# print(cur.fetchmany(2))  # 接着上次的位置向下取两条数据
# print(cur.fetchall())  # 抓取剩余的所有数据
# -------------------3.4 更新数据--------------------
# update_bumen="UPDATE bumen SET bumen_name=%s WHERE bumen_id=%s;"
# cur.execute(update_bumen, ("TEST", 3))
# cur.executemany(update_bumen, [("MAR", 2), ("BOSS", 4)])
# -------------------3.5 删除数据--------------------
delete_bumen = "DELETE FROM bumen WHERE bumen_id=%s;"
cur.execute(delete_bumen, (3, ))
cur.executemany(delete_bumen, [(2, ), (4, )])
# 4. 关闭资源以及提交
conn.commit()  # 数据写操作进行提交
cur.close()
conn.close()
